{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window LAG\n",
    "\n",
    "## COVID-19 Data\n",
    "Notes on the data: This data was assembled based on work done by [Rodrigo Pombo](https://github.com/pomber/covid19) based on [John Hopkins University](https://systems.jhu.edu/research/public-health/ncov/), based on [World Health Organisation](https://www.who.int/health-topics/coronavirus). The data was assembled 21st April 2020 - there are no plans to keep this data set up to date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "sc = (SparkSession.builder.appName('app09+') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Window Function\n",
    "The SQL Window functions include LAG, LEAD, RANK and NTILE. These functions operate over a \"window\" of rows - typically these are rows in the table that are in some sense adjacent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "covid = sc.read.table('sqlzoo.covid')\n",
    "world = sc.read.table('sqlzoo.world')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `covid` table\n",
    "\n",
    "The example uses a WHERE clause to show the cases in 'Italy' in March.\n",
    "\n",
    "**Modify the query to show data from Spain**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>day</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spain</td>\n",
       "      <td>1</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Spain</td>\n",
       "      <td>2</td>\n",
       "      <td>120</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Spain</td>\n",
       "      <td>3</td>\n",
       "      <td>165</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Spain</td>\n",
       "      <td>4</td>\n",
       "      <td>222</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Spain</td>\n",
       "      <td>5</td>\n",
       "      <td>259</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Spain</td>\n",
       "      <td>6</td>\n",
       "      <td>400</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Spain</td>\n",
       "      <td>7</td>\n",
       "      <td>500</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Spain</td>\n",
       "      <td>8</td>\n",
       "      <td>673</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Spain</td>\n",
       "      <td>9</td>\n",
       "      <td>1073</td>\n",
       "      <td>28</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Spain</td>\n",
       "      <td>10</td>\n",
       "      <td>1695</td>\n",
       "      <td>35</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Spain</td>\n",
       "      <td>11</td>\n",
       "      <td>2277</td>\n",
       "      <td>54</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Spain</td>\n",
       "      <td>12</td>\n",
       "      <td>2277</td>\n",
       "      <td>55</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Spain</td>\n",
       "      <td>13</td>\n",
       "      <td>5232</td>\n",
       "      <td>133</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Spain</td>\n",
       "      <td>14</td>\n",
       "      <td>6391</td>\n",
       "      <td>195</td>\n",
       "      <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Spain</td>\n",
       "      <td>15</td>\n",
       "      <td>7798</td>\n",
       "      <td>289</td>\n",
       "      <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Spain</td>\n",
       "      <td>16</td>\n",
       "      <td>9942</td>\n",
       "      <td>342</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Spain</td>\n",
       "      <td>17</td>\n",
       "      <td>11748</td>\n",
       "      <td>533</td>\n",
       "      <td>1028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Spain</td>\n",
       "      <td>18</td>\n",
       "      <td>13910</td>\n",
       "      <td>623</td>\n",
       "      <td>1081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Spain</td>\n",
       "      <td>19</td>\n",
       "      <td>17963</td>\n",
       "      <td>830</td>\n",
       "      <td>1107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Spain</td>\n",
       "      <td>20</td>\n",
       "      <td>20410</td>\n",
       "      <td>1043</td>\n",
       "      <td>1588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Spain</td>\n",
       "      <td>21</td>\n",
       "      <td>25374</td>\n",
       "      <td>1375</td>\n",
       "      <td>2125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Spain</td>\n",
       "      <td>22</td>\n",
       "      <td>28768</td>\n",
       "      <td>1772</td>\n",
       "      <td>2575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Spain</td>\n",
       "      <td>23</td>\n",
       "      <td>35136</td>\n",
       "      <td>2311</td>\n",
       "      <td>2575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Spain</td>\n",
       "      <td>24</td>\n",
       "      <td>39885</td>\n",
       "      <td>2808</td>\n",
       "      <td>3794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Spain</td>\n",
       "      <td>25</td>\n",
       "      <td>49515</td>\n",
       "      <td>3647</td>\n",
       "      <td>5367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Spain</td>\n",
       "      <td>26</td>\n",
       "      <td>57786</td>\n",
       "      <td>4365</td>\n",
       "      <td>7015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Spain</td>\n",
       "      <td>27</td>\n",
       "      <td>65719</td>\n",
       "      <td>5138</td>\n",
       "      <td>9357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Spain</td>\n",
       "      <td>28</td>\n",
       "      <td>73235</td>\n",
       "      <td>5982</td>\n",
       "      <td>12285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Spain</td>\n",
       "      <td>29</td>\n",
       "      <td>80110</td>\n",
       "      <td>6803</td>\n",
       "      <td>14709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Spain</td>\n",
       "      <td>30</td>\n",
       "      <td>87956</td>\n",
       "      <td>7716</td>\n",
       "      <td>16780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Spain</td>\n",
       "      <td>31</td>\n",
       "      <td>95923</td>\n",
       "      <td>8464</td>\n",
       "      <td>19259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name  day  confirmed  deaths  recovered\n",
       "0   Spain    1         84       0          2\n",
       "1   Spain    2        120       0          2\n",
       "2   Spain    3        165       1          2\n",
       "3   Spain    4        222       2          2\n",
       "4   Spain    5        259       3          2\n",
       "5   Spain    6        400       5          2\n",
       "6   Spain    7        500      10         30\n",
       "7   Spain    8        673      17         30\n",
       "8   Spain    9       1073      28         32\n",
       "9   Spain   10       1695      35         32\n",
       "10  Spain   11       2277      54        183\n",
       "11  Spain   12       2277      55        183\n",
       "12  Spain   13       5232     133        193\n",
       "13  Spain   14       6391     195        517\n",
       "14  Spain   15       7798     289        517\n",
       "15  Spain   16       9942     342        530\n",
       "16  Spain   17      11748     533       1028\n",
       "17  Spain   18      13910     623       1081\n",
       "18  Spain   19      17963     830       1107\n",
       "19  Spain   20      20410    1043       1588\n",
       "20  Spain   21      25374    1375       2125\n",
       "21  Spain   22      28768    1772       2575\n",
       "22  Spain   23      35136    2311       2575\n",
       "23  Spain   24      39885    2808       3794\n",
       "24  Spain   25      49515    3647       5367\n",
       "25  Spain   26      57786    4365       7015\n",
       "26  Spain   27      65719    5138       9357\n",
       "27  Spain   28      73235    5982      12285\n",
       "28  Spain   29      80110    6803      14709\n",
       "29  Spain   30      87956    7716      16780\n",
       "30  Spain   31      95923    8464      19259"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.functions import *\n",
    "(covid.withColumn('day', dayofmonth(covid['whn']))\n",
    "    .filter((covid['name']=='Spain') & (month(covid['whn'])==3))\n",
    "    .select('name', 'day', 'confirmed', 'deaths', 'recovered')\n",
    "    .orderBy('day')\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Introducing the LAG function\n",
    "\n",
    "The LAG function is used to show data from the preceding row or the table. When lining up rows the data is partitioned by country name and ordered by the data whn. That means that only data from Italy is considered.\n",
    "\n",
    "**Modify the query to show confirmed for the day before.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>day</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>mo</th>\n",
       "      <th>lag_cfrm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>1</td>\n",
       "      <td>1694</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2</td>\n",
       "      <td>2036</td>\n",
       "      <td>3</td>\n",
       "      <td>1694.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italy</td>\n",
       "      <td>3</td>\n",
       "      <td>2502</td>\n",
       "      <td>3</td>\n",
       "      <td>2036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>4</td>\n",
       "      <td>3089</td>\n",
       "      <td>3</td>\n",
       "      <td>2502.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Italy</td>\n",
       "      <td>5</td>\n",
       "      <td>3858</td>\n",
       "      <td>3</td>\n",
       "      <td>3089.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Italy</td>\n",
       "      <td>6</td>\n",
       "      <td>4636</td>\n",
       "      <td>3</td>\n",
       "      <td>3858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Italy</td>\n",
       "      <td>7</td>\n",
       "      <td>5883</td>\n",
       "      <td>3</td>\n",
       "      <td>4636.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Italy</td>\n",
       "      <td>8</td>\n",
       "      <td>7375</td>\n",
       "      <td>3</td>\n",
       "      <td>5883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>9</td>\n",
       "      <td>9172</td>\n",
       "      <td>3</td>\n",
       "      <td>7375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Italy</td>\n",
       "      <td>10</td>\n",
       "      <td>10149</td>\n",
       "      <td>3</td>\n",
       "      <td>9172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>11</td>\n",
       "      <td>12462</td>\n",
       "      <td>3</td>\n",
       "      <td>10149.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Italy</td>\n",
       "      <td>12</td>\n",
       "      <td>12462</td>\n",
       "      <td>3</td>\n",
       "      <td>12462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Italy</td>\n",
       "      <td>13</td>\n",
       "      <td>17660</td>\n",
       "      <td>3</td>\n",
       "      <td>12462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Italy</td>\n",
       "      <td>14</td>\n",
       "      <td>21157</td>\n",
       "      <td>3</td>\n",
       "      <td>17660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Italy</td>\n",
       "      <td>15</td>\n",
       "      <td>24747</td>\n",
       "      <td>3</td>\n",
       "      <td>21157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Italy</td>\n",
       "      <td>16</td>\n",
       "      <td>27980</td>\n",
       "      <td>3</td>\n",
       "      <td>24747.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Italy</td>\n",
       "      <td>17</td>\n",
       "      <td>31506</td>\n",
       "      <td>3</td>\n",
       "      <td>27980.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Italy</td>\n",
       "      <td>18</td>\n",
       "      <td>35713</td>\n",
       "      <td>3</td>\n",
       "      <td>31506.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Italy</td>\n",
       "      <td>19</td>\n",
       "      <td>41035</td>\n",
       "      <td>3</td>\n",
       "      <td>35713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Italy</td>\n",
       "      <td>20</td>\n",
       "      <td>47021</td>\n",
       "      <td>3</td>\n",
       "      <td>41035.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Italy</td>\n",
       "      <td>21</td>\n",
       "      <td>53578</td>\n",
       "      <td>3</td>\n",
       "      <td>47021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Italy</td>\n",
       "      <td>22</td>\n",
       "      <td>59138</td>\n",
       "      <td>3</td>\n",
       "      <td>53578.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Italy</td>\n",
       "      <td>23</td>\n",
       "      <td>63927</td>\n",
       "      <td>3</td>\n",
       "      <td>59138.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Italy</td>\n",
       "      <td>24</td>\n",
       "      <td>69176</td>\n",
       "      <td>3</td>\n",
       "      <td>63927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Italy</td>\n",
       "      <td>25</td>\n",
       "      <td>74386</td>\n",
       "      <td>3</td>\n",
       "      <td>69176.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Italy</td>\n",
       "      <td>26</td>\n",
       "      <td>80589</td>\n",
       "      <td>3</td>\n",
       "      <td>74386.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Italy</td>\n",
       "      <td>27</td>\n",
       "      <td>86498</td>\n",
       "      <td>3</td>\n",
       "      <td>80589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Italy</td>\n",
       "      <td>28</td>\n",
       "      <td>92472</td>\n",
       "      <td>3</td>\n",
       "      <td>86498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Italy</td>\n",
       "      <td>29</td>\n",
       "      <td>97689</td>\n",
       "      <td>3</td>\n",
       "      <td>92472.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Italy</td>\n",
       "      <td>30</td>\n",
       "      <td>101739</td>\n",
       "      <td>3</td>\n",
       "      <td>97689.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Italy</td>\n",
       "      <td>31</td>\n",
       "      <td>105792</td>\n",
       "      <td>3</td>\n",
       "      <td>101739.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name  day  confirmed  mo  lag_cfrm\n",
       "0   Italy    1       1694   3       NaN\n",
       "1   Italy    2       2036   3    1694.0\n",
       "2   Italy    3       2502   3    2036.0\n",
       "3   Italy    4       3089   3    2502.0\n",
       "4   Italy    5       3858   3    3089.0\n",
       "5   Italy    6       4636   3    3858.0\n",
       "6   Italy    7       5883   3    4636.0\n",
       "7   Italy    8       7375   3    5883.0\n",
       "8   Italy    9       9172   3    7375.0\n",
       "9   Italy   10      10149   3    9172.0\n",
       "10  Italy   11      12462   3   10149.0\n",
       "11  Italy   12      12462   3   12462.0\n",
       "12  Italy   13      17660   3   12462.0\n",
       "13  Italy   14      21157   3   17660.0\n",
       "14  Italy   15      24747   3   21157.0\n",
       "15  Italy   16      27980   3   24747.0\n",
       "16  Italy   17      31506   3   27980.0\n",
       "17  Italy   18      35713   3   31506.0\n",
       "18  Italy   19      41035   3   35713.0\n",
       "19  Italy   20      47021   3   41035.0\n",
       "20  Italy   21      53578   3   47021.0\n",
       "21  Italy   22      59138   3   53578.0\n",
       "22  Italy   23      63927   3   59138.0\n",
       "23  Italy   24      69176   3   63927.0\n",
       "24  Italy   25      74386   3   69176.0\n",
       "25  Italy   26      80589   3   74386.0\n",
       "26  Italy   27      86498   3   80589.0\n",
       "27  Italy   28      92472   3   86498.0\n",
       "28  Italy   29      97689   3   92472.0\n",
       "29  Italy   30     101739   3   97689.0\n",
       "30  Italy   31     105792   3  101739.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql import Window\n",
    "(covid.withColumn('day', dayofmonth(covid['whn']))\n",
    "     .withColumn('mo', month(covid['whn']))\n",
    "     .filter((covid['name']=='Italy') & (month(covid['whn'])==3))\n",
    "     .withColumn('lag_cfrm', lag(col('confirmed'))\n",
    "                 .over(Window.orderBy('day').partitionBy('name')))\n",
    "     .select('name', 'day', 'confirmed', 'mo', 'lag_cfrm')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAG operation\n",
    "\n",
    "Here is the correct query showing the cases for the day before:\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (partition by name ORDER BY whn) AS lag\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "Notice how the values in the LAG column match the value of the row diagonally above and to the left.\n",
    "\n",
    "name | DAY(whn) | confirmed | dbf\n",
    "------|---|------|----------\n",
    "Italy | 1 | **1694** | null\n",
    "Italy | 2 | 2036 | **1694**\n",
    "Italy | 3 | 2502 | 2036\n",
    "Italy | 4 | 3089 | 2502\n",
    "Italy | 5 | **3858** | 3089\n",
    "Italy | 6 | 4636 | **3858**\n",
    "Italy | 7 | 5883 | 4636\n",
    "Italy | 8 | 7375 | 5883\n",
    "Italy | 9 | 9172 | 7375\n",
    "Italy | 10 | 10149 | 9172\n",
    "... | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Number of new cases\n",
    "\n",
    "The number of confirmed case is cumulative - but we can use LAG to recover the number of new cases reported for each day.\n",
    "\n",
    "**Show the number of new cases for each day, for Italy, for March.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>day</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2</td>\n",
       "      <td>342.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italy</td>\n",
       "      <td>3</td>\n",
       "      <td>466.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>4</td>\n",
       "      <td>587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Italy</td>\n",
       "      <td>5</td>\n",
       "      <td>769.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Italy</td>\n",
       "      <td>6</td>\n",
       "      <td>778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Italy</td>\n",
       "      <td>7</td>\n",
       "      <td>1247.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Italy</td>\n",
       "      <td>8</td>\n",
       "      <td>1492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>9</td>\n",
       "      <td>1797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Italy</td>\n",
       "      <td>10</td>\n",
       "      <td>977.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>11</td>\n",
       "      <td>2313.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Italy</td>\n",
       "      <td>12</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Italy</td>\n",
       "      <td>13</td>\n",
       "      <td>5198.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Italy</td>\n",
       "      <td>14</td>\n",
       "      <td>3497.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Italy</td>\n",
       "      <td>15</td>\n",
       "      <td>3590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Italy</td>\n",
       "      <td>16</td>\n",
       "      <td>3233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Italy</td>\n",
       "      <td>17</td>\n",
       "      <td>3526.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Italy</td>\n",
       "      <td>18</td>\n",
       "      <td>4207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Italy</td>\n",
       "      <td>19</td>\n",
       "      <td>5322.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Italy</td>\n",
       "      <td>20</td>\n",
       "      <td>5986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Italy</td>\n",
       "      <td>21</td>\n",
       "      <td>6557.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Italy</td>\n",
       "      <td>22</td>\n",
       "      <td>5560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Italy</td>\n",
       "      <td>23</td>\n",
       "      <td>4789.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Italy</td>\n",
       "      <td>24</td>\n",
       "      <td>5249.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Italy</td>\n",
       "      <td>25</td>\n",
       "      <td>5210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Italy</td>\n",
       "      <td>26</td>\n",
       "      <td>6203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Italy</td>\n",
       "      <td>27</td>\n",
       "      <td>5909.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Italy</td>\n",
       "      <td>28</td>\n",
       "      <td>5974.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Italy</td>\n",
       "      <td>29</td>\n",
       "      <td>5217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Italy</td>\n",
       "      <td>30</td>\n",
       "      <td>4050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Italy</td>\n",
       "      <td>31</td>\n",
       "      <td>4053.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name  day     new\n",
       "0   Italy    1     NaN\n",
       "1   Italy    2   342.0\n",
       "2   Italy    3   466.0\n",
       "3   Italy    4   587.0\n",
       "4   Italy    5   769.0\n",
       "5   Italy    6   778.0\n",
       "6   Italy    7  1247.0\n",
       "7   Italy    8  1492.0\n",
       "8   Italy    9  1797.0\n",
       "9   Italy   10   977.0\n",
       "10  Italy   11  2313.0\n",
       "11  Italy   12     0.0\n",
       "12  Italy   13  5198.0\n",
       "13  Italy   14  3497.0\n",
       "14  Italy   15  3590.0\n",
       "15  Italy   16  3233.0\n",
       "16  Italy   17  3526.0\n",
       "17  Italy   18  4207.0\n",
       "18  Italy   19  5322.0\n",
       "19  Italy   20  5986.0\n",
       "20  Italy   21  6557.0\n",
       "21  Italy   22  5560.0\n",
       "22  Italy   23  4789.0\n",
       "23  Italy   24  5249.0\n",
       "24  Italy   25  5210.0\n",
       "25  Italy   26  6203.0\n",
       "26  Italy   27  5909.0\n",
       "27  Italy   28  5974.0\n",
       "28  Italy   29  5217.0\n",
       "29  Italy   30  4050.0\n",
       "30  Italy   31  4053.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(covid.withColumn('day', dayofmonth(covid['whn']))\n",
    "     .filter((covid['name']=='Italy') & (month(covid['whn'])==3))\n",
    "     .withColumn('new', col('confirmed') - lag(col('confirmed')).over(\n",
    "         Window.orderBy('day').partitionBy('name')))\n",
    "     .select('name', 'day', 'new')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Weekly changes\n",
    "\n",
    "The data gathered are necessarily estimates and are inaccurate. However by taking a longer time span we can mitigate some of the effects.\n",
    "\n",
    "You can filter the data to view only Monday's figures **WHERE WEEKDAY(whn) = 0**.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>whn</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-16</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-23</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>1539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-08</td>\n",
       "      <td>5681.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>17372.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>34391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>38551.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>31259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>27415.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>22609.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name         whn      new\n",
       "0   Italy  2020-01-26      NaN\n",
       "1   Italy  2020-02-02      2.0\n",
       "2   Italy  2020-02-09      1.0\n",
       "3   Italy  2020-02-16      0.0\n",
       "4   Italy  2020-02-23    152.0\n",
       "5   Italy  2020-03-01   1539.0\n",
       "6   Italy  2020-03-08   5681.0\n",
       "7   Italy  2020-03-15  17372.0\n",
       "8   Italy  2020-03-22  34391.0\n",
       "9   Italy  2020-03-29  38551.0\n",
       "10  Italy  2020-04-05  31259.0\n",
       "11  Italy  2020-04-12  27415.0\n",
       "12  Italy  2020-04-19  22609.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(covid.filter((covid['name']=='Italy') & (dayofweek(covid['whn'])==1))\n",
    "     .withColumn('new', col('confirmed')-lag(col('confirmed'))\n",
    "                 .over(Window.orderBy('whn').partitionBy('name')))\n",
    "     .select('name', 'whn', 'new')\n",
    "     .toPandas()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. LAG using a JOIN\n",
    "\n",
    "You can JOIN a table using DATE arithmetic. This will give different results if data is missing.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**\n",
    "\n",
    "In the sample query we JOIN this week tw with last week lw using the DATE_ADD function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>whn</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-16</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-23</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>1539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-08</td>\n",
       "      <td>5681.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>17372.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>34391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>38551.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>31259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>27415.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>22609.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name         whn      new\n",
       "0   Italy  2020-01-26      NaN\n",
       "1   Italy  2020-02-02      2.0\n",
       "2   Italy  2020-02-09      1.0\n",
       "3   Italy  2020-02-16      0.0\n",
       "4   Italy  2020-02-23    152.0\n",
       "5   Italy  2020-03-01   1539.0\n",
       "6   Italy  2020-03-08   5681.0\n",
       "7   Italy  2020-03-15  17372.0\n",
       "8   Italy  2020-03-22  34391.0\n",
       "9   Italy  2020-03-29  38551.0\n",
       "10  Italy  2020-04-05  31259.0\n",
       "11  Italy  2020-04-12  27415.0\n",
       "12  Italy  2020-04-19  22609.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = covid.filter((covid['name']=='Italy') & (dayofweek(covid['whn'])==1))\n",
    "(a.select('whn', 'name', 'confirmed')\n",
    "     .join(a\n",
    "           .withColumn('whn', date_add(a['whn'], 7))\n",
    "           .withColumnRenamed('confirmed', 'confirmed2')\n",
    "           .select('whn', 'name', 'confirmed2'), \n",
    "           ['whn', 'name'], how='left')\n",
    "     .withColumn('new', col('confirmed')-col('confirmed2'))\n",
    "     .select('name', 'whn', 'new')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. RANK()\n",
    "\n",
    "The query shown shows the number of confirmed cases together with the world ranking for cases.\n",
    "\n",
    "United States has the highest number, Spain is number 2...\n",
    "\n",
    "Notice that while Spain has the second highest confirmed cases, Italy has the second highest number of deaths due to the virus.\n",
    "\n",
    "**Include the ranking for the number of deaths in the table. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>rc1</th>\n",
       "      <th>deaths</th>\n",
       "      <th>rc2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>784326</td>\n",
       "      <td>1</td>\n",
       "      <td>42094</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Spain</td>\n",
       "      <td>200210</td>\n",
       "      <td>2</td>\n",
       "      <td>20852</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italy</td>\n",
       "      <td>181228</td>\n",
       "      <td>3</td>\n",
       "      <td>24114</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>156480</td>\n",
       "      <td>4</td>\n",
       "      <td>20292</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Germany</td>\n",
       "      <td>147065</td>\n",
       "      <td>5</td>\n",
       "      <td>4862</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Angola</td>\n",
       "      <td>24</td>\n",
       "      <td>86</td>\n",
       "      <td>2</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Malawi</td>\n",
       "      <td>17</td>\n",
       "      <td>87</td>\n",
       "      <td>2</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Burundi</td>\n",
       "      <td>5</td>\n",
       "      <td>88</td>\n",
       "      <td>1</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>South Sudan</td>\n",
       "      <td>4</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             name  confirmed  rc1  deaths  rc2\n",
       "0   United States     784326    1   42094    1\n",
       "1           Spain     200210    2   20852    3\n",
       "2           Italy     181228    3   24114    2\n",
       "3          France     156480    4   20292    4\n",
       "4         Germany     147065    5    4862    8\n",
       "..            ...        ...  ...     ...  ...\n",
       "85         Angola         24   86       2   77\n",
       "86         Malawi         17   87       2   77\n",
       "87        Burundi          5   88       1   79\n",
       "88    South Sudan          4   89       0   81\n",
       "89          Yemen          1   90       0   81\n",
       "\n",
       "[90 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (covid.join(world.select('name', 'population'), 'name')\n",
    "     .filter((col('whn')=='2020-04-20') & (col('population')>=1e7)))\n",
    "(a.withColumn('rc1', rank().over(Window.orderBy(col('confirmed').desc())\n",
    "                                .partitionBy('whn')))\n",
    "    .withColumn('rc2', rank().over(Window.orderBy(col('deaths').desc())\n",
    "                                  .partitionBy('whn')))\n",
    "    .select('name', 'confirmed', 'rc1', 'deaths', 'rc2')\n",
    "    .orderBy(col('confirmed').desc())\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Infection rate\n",
    "\n",
    "The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).\n",
    "\n",
    "**Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>r_inf</th>\n",
       "      <th>rc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>India</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>United States</td>\n",
       "      <td>238.0</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Jordan</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>48.0</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>143.0</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>203.0</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>14.0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  r_inf  rc\n",
       "0                China    6.0  50\n",
       "1                India    1.0  23\n",
       "2        United States  238.0  87\n",
       "3            Indonesia    3.0  35\n",
       "4             Pakistan    4.0  41\n",
       "..                 ...    ...  ..\n",
       "85              Jordan    4.0  41\n",
       "86  Dominican Republic   48.0  73\n",
       "87              Sweden  143.0  81\n",
       "88            Portugal  203.0  85\n",
       "89          Azerbaijan   14.0  62\n",
       "\n",
       "[90 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #6\n",
    "(a.withColumn('r_inf', round(1e5*a['confirmed']/a['population']))\n",
    "     .withColumn('rc', rank().over(Window.orderBy(col('r_inf'))\n",
    "                                  .partitionBy('whn')))\n",
    "     .orderBy(col('population').desc())\n",
    "     .select('name', 'r_inf', 'rc')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Turning the corner\n",
    "\n",
    "For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>whn</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>15136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-21</td>\n",
       "      <td>6557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>2020-03-23</td>\n",
       "      <td>1321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Israel</td>\n",
       "      <td>2020-03-25</td>\n",
       "      <td>1131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Spain</td>\n",
       "      <td>2020-03-25</td>\n",
       "      <td>9630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2020-03-26</td>\n",
       "      <td>1321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Germany</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>6933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Iran</td>\n",
       "      <td>2020-03-30</td>\n",
       "      <td>3186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Canada</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>2778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>1346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>1515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>1516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>2196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>8733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>United States</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>33755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Turkey</td>\n",
       "      <td>2020-04-11</td>\n",
       "      <td>5138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>France</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>26849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Peru</td>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>2265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>2454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Japan</td>\n",
       "      <td>2020-04-17</td>\n",
       "      <td>1161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>2020-04-17</td>\n",
       "      <td>3257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2020-04-18</td>\n",
       "      <td>1132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>India</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>1893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Russia</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>6060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>2020-04-20</td>\n",
       "      <td>1426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Belarus</td>\n",
       "      <td>2020-04-20</td>\n",
       "      <td>1485</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name         whn    new\n",
       "0            China  2020-02-13  15136\n",
       "1            Italy  2020-03-21   6557\n",
       "2      Switzerland  2020-03-23   1321\n",
       "3           Israel  2020-03-25   1131\n",
       "4            Spain  2020-03-25   9630\n",
       "5          Austria  2020-03-26   1321\n",
       "6          Germany  2020-03-27   6933\n",
       "7             Iran  2020-03-30   3186\n",
       "8           Canada  2020-04-05   2778\n",
       "9      Netherlands  2020-04-10   1346\n",
       "10         Ireland  2020-04-10   1515\n",
       "11        Portugal  2020-04-10   1516\n",
       "12         Ecuador  2020-04-10   2196\n",
       "13  United Kingdom  2020-04-10   8733\n",
       "14   United States  2020-04-10  33755\n",
       "15          Turkey  2020-04-11   5138\n",
       "16          France  2020-04-12  26849\n",
       "17            Peru  2020-04-13   2265\n",
       "18         Belgium  2020-04-15   2454\n",
       "19           Japan  2020-04-17   1161\n",
       "20          Brazil  2020-04-17   3257\n",
       "21    Saudi Arabia  2020-04-18   1132\n",
       "22           India  2020-04-19   1893\n",
       "23          Russia  2020-04-19   6060\n",
       "24       Singapore  2020-04-20   1426\n",
       "25         Belarus  2020-04-20   1485"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(covid.withColumn('new', (covid['confirmed']-lag(col('confirmed')).over(\n",
    "    Window.partitionBy('name').orderBy('whn'))))\n",
    "     .fillna({'new': 0})\n",
    "     .withColumn('rc', rank().over(Window.partitionBy('name').orderBy(col('new').desc())))\n",
    "     .filter((col('rc')==1) & (col('new')>1000))\n",
    "     .select('name', 'whn', 'new')\n",
    "     .orderBy('whn', 'new')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
